﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Arc.DairyMan.DairyMan.Model;

namespace Arc.DairyMan.DairyMan.DataAccess
{
    public class dalSabhasad : AbsDal
    {
        public override int Transaction()
        {
            try
            {
                int Result = 0;
                SqlCommand cmd = Connection.CreateCommand();
                Sabhasad _Sabhasad = (Sabhasad)ModelObj;
                switch (_Sabhasad.TransactionMode)
                {
                    case "I":
                        #region Insert
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandText = _Sabhasad.FriendlyName + "_" + _Sabhasad.TransactionMode;
                        //_Sabhasad.PrimaryKey = Convert.ToInt32(cmd.ExecuteScalar());
                        //if (Convert.ToInt32(_ModelObj.PrimaryKey) > 0) 

                        cmd.Parameters.Add(new SqlParameter("@Name", _Sabhasad.Name));
                        cmd.Parameters.Add(new SqlParameter("@Gender", _Sabhasad.Gender));
                        cmd.Parameters.Add(new SqlParameter("@Code", _Sabhasad.Code));
                        cmd.Parameters.Add(new SqlParameter("@MilkType", _Sabhasad.MilkType));
                        cmd.Parameters.Add(new SqlParameter("@IsMember", _Sabhasad.IsMember));
                        cmd.Parameters.Add(new SqlParameter("@Address", _Sabhasad.Address));
                        cmd.Parameters.Add(new SqlParameter("@BankNo", _Sabhasad.BankNo));
                        cmd.Parameters.Add(new SqlParameter("@ContactNo", _Sabhasad.ContactNo));

                        Result = ExecuteCommand(cmd);

                        break;
                        #endregion
                    case "U":
                        #region Update
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandText = _Sabhasad.FriendlyName + "_" + _Sabhasad.TransactionMode;
                        //_Sabhasad.PrimaryKey = Convert.ToInt32(cmd.ExecuteScalar());
                        //if (Convert.ToInt32(_ModelObj.PrimaryKey) > 0) 

                        cmd.Parameters.Add(new SqlParameter("@Name", _Sabhasad.Name));
                        cmd.Parameters.Add(new SqlParameter("@Gender", _Sabhasad.Gender));
                        cmd.Parameters.Add(new SqlParameter("@Code", _Sabhasad.Code));
                        cmd.Parameters.Add(new SqlParameter("@MilkType", _Sabhasad.MilkType));
                        cmd.Parameters.Add(new SqlParameter("@IsMember", _Sabhasad.IsMember));
                        cmd.Parameters.Add(new SqlParameter("@Address", _Sabhasad.Address));
                        cmd.Parameters.Add(new SqlParameter("@BankNo", _Sabhasad.BankNo));
                        cmd.Parameters.Add(new SqlParameter("@ContactNo", _Sabhasad.ContactNo));
                        cmd.Parameters.Add(new SqlParameter("@SabhasadId", _Sabhasad.SabhasadId));

                        Result = ExecuteCommand(cmd); ;

                        break;
                        #endregion
                    case "D":
                        #region Delete
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandText = _Sabhasad.FriendlyName + "_" + _Sabhasad.TransactionMode;

                        cmd.Parameters.Add(new SqlParameter("@SabhasadId", _Sabhasad.PrimaryKey));

                        Result = ExecuteCommand(cmd); ;

                        break;
                        #endregion
                    default:
                        throw new Exception("Invalid Transaction Mode:" + _Sabhasad.TransactionMode);

                }
                return Result;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool Insert(AbsModel Model)
        {
            Sabhasad InsertModel = (Sabhasad)Model;
            try
            {
                Connection = new SqlConnection(ConnectionString);
                Connection.Open();
                SqlCommand cmd = Connection.CreateCommand();
                //cmd.Transaction = base.Transaction;
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.CommandText = "Sabhasad_I";
                cmd.Parameters.Add(new SqlParameter("@Name", InsertModel.Name));
                cmd.Parameters.Add(new SqlParameter("@Code", InsertModel.Code));
                cmd.Parameters.Add(new SqlParameter("@MilkType", InsertModel.MilkType));
                cmd.Parameters.Add(new SqlParameter("@IsMember", InsertModel.IsMember));
                cmd.Parameters.Add(new SqlParameter("@Address", InsertModel.Address));
                cmd.Parameters.Add(new SqlParameter("@BankNo", InsertModel.BankNo));
                cmd.Parameters.Add(new SqlParameter("@ContactNo", InsertModel.ContactNo));
                int _rowsAffected = cmd.ExecuteNonQuery();
                Connection.Close();
                return (_rowsAffected > 0);
            }
            catch
            {

                throw;
            }
        }

        public override void Select(string Critiera, DataSet Ds)
        {
            try
            {
                string _SQL = @"Select * from " + ModelObj.FriendlyName;
                if (Critiera.Trim() != string.Empty)
                    _SQL += " Where " + Critiera;
                GetDataTable(_SQL, ModelObj.FriendlyName, Ds);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public override void Search(string Critiera, DataSet Ds)
        {
            try
            {
                string _SQL = @"Select * from " + ModelObj.FriendlyName;
                if (Critiera.Trim() != "")
                    _SQL += " Where " + Critiera;
                GetDataTable(_SQL, ModelObj.FriendlyName, Ds);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        public string GetNextSabhasadCode()
        {
            string _SQL = @"Select top 1 Code from " + ModelObj.FriendlyName + " order by SabhasadId desc";

            DataTable _dtNextCode = base.GetDataTable(_SQL, "NextCode", new DataSet());

            if (_dtNextCode.Rows.Count > 0)
                return (Convert.ToInt32(_dtNextCode.Rows[0][0]) + 1).ToString();

            return "1";
        }

        public int GetSabhasadIdByCode(string Code)
        {
            string _SQL = @"Select top 1 SabhasadId from " + ModelObj.FriendlyName + " where Code = '" + Code + "'";

            DataTable _dtNextCode = base.GetDataTable(_SQL, "IdByCode", new DataSet());

            if (_dtNextCode.Rows.Count > 0)
                return Convert.ToInt32(_dtNextCode.Rows[0][0]);

            return 0;
        }
    }
}
